/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package camada.bancodedados.daoimplemente.cheques;

import camada.bancodedados.conexao.Conexao;
import camada.negocio.dao.cheques.ChequeDAO;
import com.mysql.jdbc.PreparedStatement;
import eloma.classe.SaidaCheques;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import utilitarios.Exceptions;

/**
 *
 * @author Icaro
 */
public class ChequeDAOImpl implements ChequeDAO {

    Exceptions exception = new Exceptions();

    @Override
    //<editor-fold desc="inserirCheque">
    public void inserirCheque(SaidaCheques cheques) {
        Calendar c = Calendar.getInstance();

        PreparedStatement pstm = null;
        try {
            final String SQL_INSERT
                    = "insert into SaidaCheque (numerocheque,descricao,valor,situacao,ano_cadastro,mes_cadastro) values (?,?,?,?,?,?)";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_INSERT);
            pstm.setInt(1, cheques.getNumeroCheque());
            pstm.setString(2, cheques.getDescricao());
            pstm.setFloat(3, cheques.getValorCheque());
            pstm.setString(4, cheques.getSituacao());
            pstm.setInt(5, c.get(Calendar.YEAR));
            pstm.setInt(6, c.get(Calendar.MONTH) + 1);
            pstm.executeUpdate();
        } catch (SQLException e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - inserirSaidaCheques");
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="atualizarCheque">
    public void atualizarCheque(SaidaCheques cheques) {
        PreparedStatement pstm = null;
        try {
            final String SQL_UPDATE
                    = "UPDATE SaidaCheque SET numerocheque = ?, descricao = ?,  valor = ?, situacao = ? where idSaidaCheque = ? ";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_UPDATE);
            pstm.setInt(1, cheques.getNumeroCheque());
            pstm.setString(2, cheques.getDescricao());
            pstm.setFloat(3, cheques.getValorCheque());
            pstm.setString(4, cheques.getSituacao());
            pstm.setInt(5, cheques.getIdCheque());
            pstm.executeUpdate();
        } catch (SQLException e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - atualizarSaidaCheques");
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="atualizarSituacaoCheque">
    public void atualizarSituacaoCheque(int idSaidaCheque, String situacao) {
        PreparedStatement pstm = null;
        try {
            final String SQL_UPDATE
                    = "UPDATE SaidaCheque SET situacao = ? where idSaidaCheque = ?";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_UPDATE);
            pstm.setString(1, situacao);
            pstm.setInt(2, idSaidaCheque);
            pstm.executeUpdate();
        } catch (SQLException e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - atualizarSituacaoSaidaCheques");
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="removerCheque">
    public void removerCheque(int idCheque) {
        PreparedStatement pstm = null;
        try {
            final String SQL_DELETE = "delete from SaidaCheque where idSaidaCheque = ?";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_DELETE);
            pstm.setInt(1, idCheque);
            pstm.executeUpdate();
        } catch (SQLException e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - removerSaidaCheques");
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="selecionarTodosCheque">
    public List<SaidaCheques> selecionarTodosCheque() throws Exception {
        Calendar c = Calendar.getInstance();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "Select * from SaidaCheque";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            rs = stmt.executeQuery();
            return carregarVariosResultados(rs);
        } catch (SQLException sqle) {
            exception.Exceptions(sqle.getMessage().toString(), "SaidaChequesDAOImpl - selecionarTodosSaidaCheques");
            throw new Exception(sqle);
        } catch (Exception e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - selecionarTodosSaidaCheques");
            throw new Exception(e);
        } finally {
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="selecionarChequeDoMes">
    public List<SaidaCheques> selecionarChequeDoMes() throws Exception {
        Calendar c = Calendar.getInstance();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "Select * from SaidaCheque Where Ano_Cadastro = ? And Mes_Cadastro = ?";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, c.get(Calendar.YEAR));
            stmt.setInt(2, c.get(Calendar.MONTH) + 1);
            rs = stmt.executeQuery();
            return carregarVariosResultados(rs);
        } catch (SQLException sqle) {
            exception.Exceptions(sqle.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(sqle);
        } catch (Exception e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(e);
        } finally {
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="selecionarChequeLiberadosDoMes">
    public List<SaidaCheques> selecionarChequeLiberadosDoMes() throws Exception {
        Calendar c = Calendar.getInstance();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "Select * from SaidaCheque Where Ano_Cadastro = ? And Mes_Cadastro = ? and situacao = ?";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, c.get(Calendar.YEAR));
            stmt.setInt(2, c.get(Calendar.MONTH) + 1);
            stmt.setString(3, "Liberado");
            rs = stmt.executeQuery();
            return carregarVariosResultados(rs);
        } catch (SQLException sqle) {
            exception.Exceptions(sqle.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(sqle);
        } catch (Exception e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(e);
        } finally {
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="selecionarChequePendentesDoMes">
    public List<SaidaCheques> selecionarChequePendentesDoMes() throws Exception {
        Calendar c = Calendar.getInstance();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "Select * from SaidaCheque Where Ano_Cadastro = ? And Mes_Cadastro = ? and situacao = ?";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, c.get(Calendar.YEAR));
            stmt.setInt(2, c.get(Calendar.MONTH) + 1);
            stmt.setString(3, "Pendente");
            rs = stmt.executeQuery();
            return carregarVariosResultados(rs);
        } catch (SQLException sqle) {
            exception.Exceptions(sqle.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(sqle);
        } catch (Exception e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(e);
        } finally {
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="selecionarChequePorNumeroCheque">
    public List<SaidaCheques> selecionarChequePorNumeroCheque(int numeroCheque) throws Exception {
        Calendar c = Calendar.getInstance();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "Select * from SaidaCheque Where numeroCheque = ?";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, numeroCheque);
            rs = stmt.executeQuery();
            return carregarVariosResultados(rs);
        } catch (SQLException sqle) {
            exception.Exceptions(sqle.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(sqle);
        } catch (Exception e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(e);
        } finally {
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="selecionarChequePorData">
    public List<SaidaCheques> selecionarChequePorData(int ano, int mes) throws Exception {
        Calendar c = Calendar.getInstance();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "Select * from SaidaCheque Where Ano_Cadastro = ? And Mes_Cadastro = ? ";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, ano);
            stmt.setInt(2, mes);
            rs = stmt.executeQuery();
            return carregarVariosResultados(rs);
        } catch (SQLException sqle) {
            exception.Exceptions(sqle.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(sqle);
        } catch (Exception e) {
            exception.Exceptions(e.getMessage().toString(), "SaidaChequesDAOImpl - selecionarSaidaChequesAbertosBaixadosPorAnoEMes");
            throw new Exception(e);
        } finally {
        }
    }
    //</editor-fold>

    //<editor-fold desc="Metódos para carregar listas">
    public List<SaidaCheques> carregarVariosResultados(ResultSet rs) throws SQLException {
        List<SaidaCheques> resultList = new ArrayList<SaidaCheques>();
        while (rs.next()) {
            SaidaCheques dto = new SaidaCheques();
            carregarVO(dto, rs);
            resultList.add(dto);
        }
        return resultList;
    }

    private SaidaCheques carregarSimpleResultado(ResultSet rs) throws SQLException {
        if (rs.next()) {
            SaidaCheques dto = new SaidaCheques();
            carregarVO(dto, rs);
            return dto;
        } else {
            return null;
        }
    }

    private void carregarVO(SaidaCheques dto, ResultSet rs) throws SQLException {
        dto.setIdCheque(rs.getInt("IdSaidaCheque"));
        dto.setNumeroCheque(rs.getInt("NumeroCheque"));
        dto.setDescricao(rs.getString("Descricao"));
        dto.setValorCheque(rs.getFloat("valor"));
        dto.setSituacao(rs.getString("situacao"));
    }
    //</editor-fold>
}
